package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.bo.CustomerInformationBO;
import com.kcs.fxmargin.model.MCustInfo;

public class CustInfoDao {
	
	public static final String formatDate = "dd/MM/yyyy HH:mm";

	public List<CustomerInformationBO> searchCustomerInfo(Connection conn, PreparedStatement pstm, ResultSet rs, MCustInfo custInfo) throws Exception{
	
		StringBuffer sql 							= new StringBuffer();
		List<CustomerInformationBO> custtomerList 	= new ArrayList<CustomerInformationBO>();
		CustomerInformationBO cus 					= null;
		
		sql.append(" select c.id, c.cif_no, c.cust_name, c.margin_acc_no, c.margin_acc_name, ");
		sql.append(" c.deposit_acc_no, c.deposit_acc_name ");
		sql.append(" , c.cust_limit_amt, u.user_name update_by, c.update_dt, s.name status_name, c.cust_status_code, c.row_version ");
		sql.append(" from m_cust_info c, c_cust_status s, m_users u ");
		sql.append(" where c.cust_status_code=s.code ");
		sql.append(" and c.update_by=u.user_id ");
		sql.append(" and c.active_status='A' ");
		sql.append(" and s.active_status='A' ");
		
		if(custInfo.getCifNo() != null && !"".equals(custInfo.getCifNo())) sql.append(" and c.cif_no = ? ");
		if(custInfo.getCustName() != null && !"".equals(custInfo.getCustName())) sql.append(" and UPPER(c.cust_name) like UPPER(?) ");
		
		sql.append(" order by cif_no ");
		
		System.out.println("SQL -> " + sql.toString());
		
		int index = 1;
		pstm = conn.prepareStatement(sql.toString());
		
		if(custInfo.getCifNo() != null && !"".equals(custInfo.getCifNo())){
			pstm.setString(index, custInfo.getCifNo());
			index++;
		}
		
		if(custInfo.getCustName() != null && !"".equals(custInfo.getCustName())){
			pstm.setString(index, custInfo.getCustName());
			index++;
		}
		
		rs = pstm.executeQuery();
		index = 1;
		
		while(rs.next()){
			cus = new CustomerInformationBO();
			cus.setRowNum(index);
			cus.setId(rs.getString("id"));
			cus.setCifNo(rs.getString("cif_no"));
			cus.setCustomerName(rs.getString("cust_name"));
			cus.setMarginAccNo(rs.getString("margin_acc_no"));
			cus.setMarginAccName(rs.getString("margin_acc_name"));
			cus.setDepositAccNo(rs.getString("deposit_acc_no"));
			cus.setDepositAccName(rs.getString("deposit_acc_name"));
			cus.setCustLimitAmt(rs.getBigDecimal("cust_limit_amt"));
			cus.setUpdateBy(rs.getString("update_by"));
			cus.setUpdateDate(commonUtils.formatDate(rs.getDate("update_dt"), formatDate));
			cus.setCustomerStatus(rs.getString("status_name"));
			cus.setCustomerStatusCode(rs.getString("cust_status_code"));
			cus.setRowVersion(rs.getInt("row_version"));
			cus.setJsonObject();
			
			custtomerList.add(cus);
			index++;
		}
		
		System.out.println("custtomerList size = " + custtomerList.size());
		
		return custtomerList;
	}
	
	public String insertCustomerInfo(Connection conn, PreparedStatement pstm, MCustInfo custInfo) throws Exception {
		
		StringBuffer sql 		= new StringBuffer();
		
		int insert = 0;
		
		sql.append(" insert into m_cust_info ");
		sql.append(" (id, cif_no, cust_name, margin_acc_no, margin_acc_name, deposit_acc_no, deposit_acc_name,  ");
		sql.append(" cust_limit_amt, cust_status_code, active_status, create_by, create_dt, update_by, update_dt, row_version) ");
		sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, getDate(), ?, getDate(), ?) ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, custInfo.getId());
		pstm.setString(2, custInfo.getCifNo());
		pstm.setString(3, custInfo.getCustName());
		pstm.setString(4, custInfo.getMarginAccNo());
		pstm.setString(5, custInfo.getMarginAccName());
		pstm.setString(6, custInfo.getDepositAccNo());
		pstm.setString(7, custInfo.getDepositAccName());
		pstm.setBigDecimal(8, custInfo.getCustLimitAmt());
		pstm.setString(9, custInfo.getCustStatusCode());
		pstm.setString(10, custInfo.getActiveStatus());
		pstm.setString(11, custInfo.getCreateby());
		pstm.setString(12, custInfo.getUpdateBy());
		pstm.setInt(13, 1);
		
		insert = pstm.executeUpdate();
		
		System.out.println("insert = " + insert);
		
		return insert == 1 ? "s" : "e";
	}
	
	public String updateCustomerInfo(Connection conn, PreparedStatement pstm, MCustInfo custInfo) throws Exception {
		
		StringBuffer sql 		= new StringBuffer();
		
		int update = 0;
		
		sql.append(" update m_cust_info set  ");
		sql.append(" cif_no = ?, ");
		sql.append(" cust_name = ?, ");
		sql.append(" margin_acc_no = ?, ");
		sql.append(" margin_acc_name = ?, ");
		sql.append(" deposit_acc_no = ?, ");
		sql.append(" deposit_acc_name = ?, ");
		sql.append(" cust_limit_amt = ?, ");
		sql.append(" cust_status_code = ?, ");
		sql.append(" update_by = ?, ");
		sql.append(" update_dt = getDate(), ");
		sql.append(" row_version = ? ");
		sql.append(" where id = ? "); 	
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, custInfo.getCifNo());
		pstm.setString(2, custInfo.getCustName());
		pstm.setString(3, custInfo.getMarginAccNo());
		pstm.setString(4, custInfo.getMarginAccName());
		pstm.setString(5, custInfo.getDepositAccNo());
		pstm.setString(6, custInfo.getDepositAccName());
		pstm.setBigDecimal(7, custInfo.getCustLimitAmt());
		pstm.setString(8, custInfo.getCustStatusCode());
		pstm.setString(9, custInfo.getUpdateBy());
		pstm.setInt(10, custInfo.getRowVersion());
		pstm.setString(11, custInfo.getId());
		
		update = pstm.executeUpdate();
		
		System.out.println("update = " + update);
		
		return update == 1 ? "s" : "e";
	}
	
	public String delete(Connection conn, PreparedStatement pstm, String id) throws Exception {
		
		StringBuffer sql 		= new StringBuffer();
		
		int delete = 0;
		
		sql.append(" delete from m_cust_info where id = ? ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, id);
		delete = pstm.executeUpdate();
		
		System.out.println("delete = " + delete);
		
		return delete == 1 ? "s" : "e";
	}
}
